/************************************************************************************/
/*  Program name CAPPOEXT_PURCHASE_ORDER_LINE_LOCATION.sql                          */
/*  Creater:  Kenny, Leung (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 16 Jan 2006, Kenny Leung         First created                               */
/************************************************************************************/
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPPOEXT_PURCHASE_ORDER_LINE_LOCATION';

	lv_Scc_Count	NUMBER	:=	0;
	lv_Err_Count	NUMBER	:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	lv_FileName		VARCHAR2(100)	:=	'CAPPO_PURCHASE_ORDER_LINE_LOCATION.dat';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;

	lv_NGO_Name		VARCHAR2(100);
	lv_NGO_Count	NUMBER;

	CURSOR Cur_F(p_NGO VARCHAR2) IS
	SELECT PLL.* 
    FROM   PO_LINE_LOCATIONS_ALL PLL,
            HR_ORGANIZATION_UNITS HOU
    WHERE  PLL.ORG_ID = HOU.ORGANIZATION_ID
    AND    HOU.NAME LIKE 'OU_' ||p_NGO||'%';


BEGIN
	lv_NGO_Name		:=	SUBSTR(UPPER('&1'), -4);

	SELECT	COUNT('A')
	INTO	lv_NGO_Count
	FROM	HR_ORGANIZATION_UNITS
	WHERE	SUBSTR(Name,4,4)	=	lv_NGO_Name;

	IF	lv_NGO_Count	>=	1	THEN
        
        lv_UTL_File		:=	UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
        
		FOR C1 IN Cur_F(lv_NGO_Name) LOOP
			
			UTL_FILE.PUT_LINE(
				      lv_UTL_File,
				      C1.LINE_LOCATION_ID                ||CHR(9)||
                      C1.LAST_UPDATE_DATE                ||CHR(9)||
                      C1.LAST_UPDATED_BY                 ||CHR(9)||
                      C1.PO_HEADER_ID                    ||CHR(9)||
                      C1.PO_LINE_ID                      ||CHR(9)||
                      C1.LAST_UPDATE_LOGIN               ||CHR(9)||
                      C1.CREATION_DATE                   ||CHR(9)||
                      C1.CREATED_BY                      ||CHR(9)||
                      C1.QUANTITY                        ||CHR(9)||
                      C1.QUANTITY_RECEIVED               ||CHR(9)||
                      C1.QUANTITY_ACCEPTED               ||CHR(9)||
                      C1.QUANTITY_REJECTED               ||CHR(9)||
                      C1.QUANTITY_BILLED                 ||CHR(9)||
                      C1.QUANTITY_CANCELLED              ||CHR(9)||
                      C1.UNIT_MEAS_LOOKUP_CODE           ||CHR(9)||
                      C1.PO_RELEASE_ID                   ||CHR(9)||
                      C1.SHIP_TO_LOCATION_ID             ||CHR(9)||
                      C1.SHIP_VIA_LOOKUP_CODE            ||CHR(9)||
                      C1.NEED_BY_DATE                    ||CHR(9)||
                      C1.PROMISED_DATE                   ||CHR(9)||
                      C1.LAST_ACCEPT_DATE                ||CHR(9)||
                      C1.PRICE_OVERRIDE                  ||CHR(9)||
                      C1.ENCUMBERED_FLAG                 ||CHR(9)||
                      C1.ENCUMBERED_DATE                 ||CHR(9)||
                      C1.UNENCUMBERED_QUANTITY           ||CHR(9)||
                      C1.FOB_LOOKUP_CODE                 ||CHR(9)||
                      C1.FREIGHT_TERMS_LOOKUP_CODE       ||CHR(9)||
                      C1.TAXABLE_FLAG                    ||CHR(9)||
                      C1.TAX_NAME                        ||CHR(9)||
                      C1.ESTIMATED_TAX_AMOUNT            ||CHR(9)||
                      C1.FROM_HEADER_ID                  ||CHR(9)||
                      C1.FROM_LINE_ID                    ||CHR(9)||
                      C1.FROM_LINE_LOCATION_ID           ||CHR(9)||
                      C1.START_DATE                      ||CHR(9)||
                      C1.END_DATE                        ||CHR(9)||
                      C1.LEAD_TIME                       ||CHR(9)||
                      C1.LEAD_TIME_UNIT                  ||CHR(9)||
                      C1.PRICE_DISCOUNT                  ||CHR(9)||
                      C1.TERMS_ID                        ||CHR(9)||
                      C1.APPROVED_FLAG                   ||CHR(9)||
                      C1.APPROVED_DATE                   ||CHR(9)||
                      C1.CLOSED_FLAG                     ||CHR(9)||
                      C1.CANCEL_FLAG                     ||CHR(9)||
                      C1.CANCELLED_BY                    ||CHR(9)||
                      C1.CANCEL_DATE                     ||CHR(9)||
                      C1.CANCEL_REASON                   ||CHR(9)||
                      C1.FIRM_STATUS_LOOKUP_CODE         ||CHR(9)||
                      C1.FIRM_DATE                       ||CHR(9)||
                      C1.ATTRIBUTE_CATEGORY              ||CHR(9)||
                      C1.ATTRIBUTE1                      ||CHR(9)||
                      C1.ATTRIBUTE2                      ||CHR(9)||
                      C1.ATTRIBUTE3                      ||CHR(9)||
                      C1.ATTRIBUTE4                      ||CHR(9)||
                      C1.ATTRIBUTE5                      ||CHR(9)||
                      C1.ATTRIBUTE6                      ||CHR(9)||
                      C1.ATTRIBUTE7                      ||CHR(9)||
                      C1.ATTRIBUTE8                      ||CHR(9)||
                      C1.ATTRIBUTE9                      ||CHR(9)||
                      C1.ATTRIBUTE10                     ||CHR(9)||
                      C1.UNIT_OF_MEASURE_CLASS           ||CHR(9)||
                      C1.ENCUMBER_NOW                    ||CHR(9)||
                      C1.ATTRIBUTE11                     ||CHR(9)||
                      C1.ATTRIBUTE12                     ||CHR(9)||
                      C1.ATTRIBUTE13                     ||CHR(9)||
                      C1.ATTRIBUTE14                     ||CHR(9)||
                      C1.ATTRIBUTE15                     ||CHR(9)||
                      C1.INSPECTION_REQUIRED_FLAG        ||CHR(9)||
                      C1.RECEIPT_REQUIRED_FLAG           ||CHR(9)||
                      C1.QTY_RCV_TOLERANCE               ||CHR(9)||
                      C1.QTY_RCV_EXCEPTION_CODE          ||CHR(9)||
                      C1.ENFORCE_SHIP_TO_LOCATION_CODE   ||CHR(9)||
                      C1.ALLOW_SUBSTITUTE_RECEIPTS_FLAG  ||CHR(9)||
                      C1.DAYS_EARLY_RECEIPT_ALLOWED      ||CHR(9)||
                      C1.DAYS_LATE_RECEIPT_ALLOWED       ||CHR(9)||
                      C1.RECEIPT_DAYS_EXCEPTION_CODE     ||CHR(9)||
                      C1.INVOICE_CLOSE_TOLERANCE         ||CHR(9)||
                      C1.RECEIVE_CLOSE_TOLERANCE         ||CHR(9)||
                      C1.SHIP_TO_ORGANIZATION_ID         ||CHR(9)||
                      C1.SHIPMENT_NUM                    ||CHR(9)||
                      C1.SOURCE_SHIPMENT_ID              ||CHR(9)||
                      C1.SHIPMENT_TYPE                   ||CHR(9)||
                      C1.CLOSED_CODE                     ||CHR(9)||
                      C1.REQUEST_ID                      ||CHR(9)||
                      C1.PROGRAM_APPLICATION_ID          ||CHR(9)||
                      C1.PROGRAM_ID                      ||CHR(9)||
                      C1.PROGRAM_UPDATE_DATE             ||CHR(9)||
                      C1.USSGL_TRANSACTION_CODE          ||CHR(9)||
                      C1.GOVERNMENT_CONTEXT              ||CHR(9)||
                      C1.RECEIVING_ROUTING_ID            ||CHR(9)||
                      C1.ACCRUE_ON_RECEIPT_FLAG          ||CHR(9)||
                      C1.CLOSED_REASON                   ||CHR(9)||
                      C1.CLOSED_DATE                     ||CHR(9)||
                      C1.CLOSED_BY                       ||CHR(9)||
                      C1.ORG_ID                          ||CHR(9)||
                      C1.QUANTITY_SHIPPED                ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE_CATEGORY       ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE1               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE2               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE3               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE4               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE5               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE6               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE7               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE8               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE9               ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE10              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE11              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE12              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE13              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE14              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE15              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE16              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE17              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE18              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE19              ||CHR(9)||
                      C1.GLOBAL_ATTRIBUTE20              ||CHR(9)||
                      C1.COUNTRY_OF_ORIGIN_CODE          ||CHR(9)||
                      C1.TAX_USER_OVERRIDE_FLAG          ||CHR(9)||
                      C1.MATCH_OPTION                    ||CHR(9)||
                      C1.TAX_CODE_ID                     ||CHR(9)||
                      C1.CALCULATE_TAX_FLAG              ||CHR(9)||
                      C1.CHANGE_PROMISED_DATE_REASON     ||CHR(9)||
                      C1.NOTE_TO_RECEIVER                ||CHR(9)||
                      C1.SECONDARY_QUANTITY              ||CHR(9)||
                      C1.SECONDARY_UNIT_OF_MEASURE       ||CHR(9)||
                      C1.PREFERRED_GRADE                 ||CHR(9)||
                      C1.SECONDARY_QUANTITY_RECEIVED     ||CHR(9)||
                      C1.SECONDARY_QUANTITY_ACCEPTED     ||CHR(9)||
                      C1.SECONDARY_QUANTITY_REJECTED     ||CHR(9)||
                      C1.SECONDARY_QUANTITY_CANCELLED    ||CHR(9)||
                      C1.VMI_FLAG                        ||CHR(9)||
                      C1.CONSIGNED_FLAG                  ||CHR(9)||
                      C1.RETROACTIVE_DATE                ||CHR(9)||
                      C1.SUPPLIER_ORDER_LINE_NUMBER      
			);

			lv_Scc_Count	:=	lv_Scc_Count	+	1;
		END LOOP;
		UTL_FILE.FClose(lv_UTL_File);
	ELSE
		lv_Err_Count	:=	lv_Err_Count + 1;
		lv_Err_Msg		:=	lv_NGO_Name||': No PO Line Locations for NGO';
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/

